package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Newscategory;
import com.ysd.util.DBManager;

public class NewscategoryDaoImpl { // 实现类,起名: 实体类名DaoImpl
	public List<Newscategory> getNewscategory() {
		List<Newscategory> list = new ArrayList<Newscategory>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newscategory";
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsCategoryName = rs.getString("newsCategoryName");
				Newscategory newscategory = new Newscategory(newsCategoryId, newsCategoryName);
				list.add(newscategory);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	//分页查询
	public List<Newscategory> getNewscategoryByPage(int page,int size) {
		List<Newscategory> list = new ArrayList<Newscategory>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newscategory limit "+(page-1)*size+", "+size;
			System.out.println(sql);
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsCategoryName = rs.getString("newsCategoryName");
				Newscategory newscategory = new Newscategory(newsCategoryId, newsCategoryName);
				list.add(newscategory);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	// 根据分类id删除新闻分类
	// nid是新闻分类的id

	public int deleteNewsCategoryById(int nid) {
		return DBManager.executeUpdate("delete from newscategory where newsCategoryId=" + nid);
	}
	/***
	 *添加新闻分类
	 *返回受影响行数 int
	 *
	 *
	 */
	public int addNewsCategory(Newscategory ne) {
		return DBManager.executeUpdate("insert into newscategory values (null,'"+ne.getNewsCategoryName()+"')");
	}
	/***
	 *查询新闻分类
	 *根据分类名
	 *
	 *
	 */
	public List<Newscategory> getNewscategoryByName(String name) {
		List<Newscategory> list = new ArrayList<Newscategory>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select *from newscategory where newsCategoryName='"+name+"'";
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsCategoryName = rs.getString("newsCategoryName");
				Newscategory newscategory = new Newscategory(newsCategoryId, newsCategoryName);
				list.add(newscategory);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/***
	 *根据id查询新闻分类
	 *返回一个分类对象 
	 * 
	 */
	
	public Newscategory getNewscategoryById(int nid) {
		Newscategory newscategory =null;
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newscategory where newsCategoryId="+nid;
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsCategoryName = rs.getString("newsCategoryName");
				newscategory = new Newscategory(newsCategoryId, newsCategoryName);
				
			}
			return newscategory;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/***
	 *修改新闻分类
	 *返回受影响行数 int
	 *
	 *
	 */
	public int updateNewsCategory(Newscategory ne) {
		return DBManager.executeUpdate("UPDATE newscategory set newsCategoryName='"+ne.getNewsCategoryName()+"' where newsCategoryId="+ne.getNewsCategoryId());
	}
	/***
	 *根据id批量删除新闻分类
	 *返回受影响行数 int
	 *
	 *
	 */
	public int delNewsCategoryByIds(String ids) {
		return DBManager.executeUpdate("delete from newscategory where newsCategoryid in("+ids+")");
	}
}
